package com.home766.dao.impl;

import com.home766.dao.SchemeDao;
import com.home766.entity.Scheme;
import com.home766.util.StringUtil;
import net.biz.Page;
import net.biz.Pageable;
import net.biz.dao.NativeBaseDao;
import net.biz.dao.impl.BaseDaoImpl;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import javax.persistence.Query;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import java.util.*;

/**
 * 方案-daoimpl
 */
@Repository("schemeDaoImpl")
public class SchemeDaoImpl extends BaseDaoImpl<Scheme, Long> implements SchemeDao{
	
	@Resource(name="nativeBaseDaoImpl")
	private NativeBaseDao nativeBaseDao;
	
	/**
	 * 检查名称是否存在
	 * @param name
	 * @return
	 */
	@Override
	public boolean checkExists(String name) {
		if (StringUtil.isNullOrSpace(name)) {
			return false;
		}
		String hql = "select count(*) from Scheme sp where lower(sp.name) = lower(:name) and sp.isDelete != 1 ";
		Long count = entityManager.createQuery(hql, Long.class).setParameter("name", name).getSingleResult();
		return count > 0;
	}
	
	/**
	 * 方案列表分页查询(带查询条件)
	 * @param pageable
	 * @param sn            方案编码
	 * @param name          方案名称
	 * @param createPerson  创建人
	 * @param createDate    创建时间
	 * @param type          方案类型
	 * @param isMarketable  是否已上架
	 * @return
	 */
	@SuppressWarnings("static-access")
	@Override
	public Page<Scheme> findPage(Pageable pageable, String sn, String name, String createPerson, Date beginDate, Date endDate, Long type, Boolean isMarketable, Boolean isTehui) {
		CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
		CriteriaQuery<Scheme> criteriaQuery = criteriaBuilder.createQuery(Scheme.class);
		Root<Scheme> root = criteriaQuery.from(Scheme.class);
		criteriaQuery.select(root);
		Predicate restrictions = criteriaBuilder.conjunction();
		restrictions=criteriaBuilder.and(restrictions,criteriaBuilder.notEqual(root.<Boolean> get("isDelete"), true));
		if(!StringUtil.isNullOrSpace(sn)){
			restrictions=criteriaBuilder.and(restrictions,criteriaBuilder.like(root.<String> get("sn"), "%"+sn+"%"));
		}
		if(!StringUtil.isNullOrSpace(name)){
			restrictions=criteriaBuilder.and(restrictions,criteriaBuilder.like(root.<String> get("name"), "%"+name+"%"));
		}
		if(!StringUtil.isNullOrSpace(createPerson)){
			restrictions=criteriaBuilder.and(restrictions,criteriaBuilder.like(root.join("createPerson") .<String> get("name"), "%"+createPerson+"%"));
		}
		if(isMarketable != null){
			restrictions=criteriaBuilder.and(restrictions,criteriaBuilder.equal(root.<Boolean> get("isMarketable"), isMarketable));
		}
		if(type != null){
			restrictions=criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.<Long> get("type"), type));
		}
		Calendar calendar = new GregorianCalendar();
		if (beginDate != null) {
			restrictions = criteriaBuilder.and(restrictions, root.get("createDate").isNotNull(), criteriaBuilder.greaterThanOrEqualTo(root.<Date> get("createDate"), beginDate));
		}
		if (endDate != null) {
			calendar.setTime(endDate); 
		    calendar.add(calendar.DATE,1);//把日期往后增加一天.整数往后推,负数往前移动 
		    endDate=calendar.getTime();
			restrictions = criteriaBuilder.and(restrictions, root.get("createDate").isNotNull(), criteriaBuilder.lessThanOrEqualTo(root.<Date> get("createDate"), endDate));
		}
		if(isTehui != null && isTehui){
			restrictions = criteriaBuilder.and(restrictions, root.get("isTehui").isNotNull(), criteriaBuilder.equal(root.<Boolean> get("isTehui"), isTehui));
		}
		criteriaQuery.where(restrictions);
//		List<Order> orders = pageable.getOrders();
//		orders.add(Order.desc("isTop"));
//		orders.add(Order.desc("modifyDate"));
		return super.findPage(criteriaQuery, pageable);
	}
	
	/**
	 * 前台方案列表数据查看
	 * @param pageable
	 * @param acreage
	 * @param attribute
	 * @param brandId
	 * @return
	 */
	@Override
	public Page<Scheme> findPage(Pageable pageable, Integer acreage, Integer attribute, Long brandId, Long auxiliaryId, Boolean isTehui, Long type) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT  ");
		sql.append(" DISTINCT(s.id), ");
		sql.append(" s.sn,");
		sql.append(" s.name, ");
		sql.append(" s.type, ");
		sql.append(" s.price, ");
		sql.append(" s.image, ");
		sql.append(" s.acreage, ");
		sql.append(" s.attribute, ");
		sql.append(" s.is_marketable, ");
		sql.append(" s.create_date ");
		sql.append(" FROM 766_scheme s ");
		sql.append(" LEFT JOIN 766_scheme_scheme_product ssp ");
		sql.append(" ON ssp.766_scheme = s.id ");
		sql.append(" LEFT JOIN 766_scheme_product sp ");
		sql.append(" ON sp.id = ssp.scheme_products ");
		sql.append(" LEFT JOIN xx_brand xb ");
		sql.append(" ON xb.id = sp.brand ");
		sql.append("  WHERE 1 = 1 ");
		sql.append("  AND s.is_delete != 1 ");
		sql.append("  AND s.is_marketable = 1 ");
		Map<String,Object> map = new HashMap<String, Object>();
		if(type != null){
			sql.append(" and s.type = :type ");
			map.put("type", type);
		}
		if(acreage != null){
			sql.append(" and s.area_tag = :acreage ");
			map.put("acreage", acreage);
		}
		if(attribute != null){
			sql.append(" and s.attribute like :attribute ");
			map.put("attribute", "%"+attribute+"%");
		}
		if(brandId != null){
			sql.append(" and (xb.id = :brand ");
			map.put("brand", brandId);
			if(auxiliaryId !=null){
				sql.append(" or xb.id = :brandAuxiliaryId)");
				map.put("brandAuxiliaryId", auxiliaryId);
			}else{
				sql.append(")");
			}
		}else{
			if(auxiliaryId !=null){
				sql.append(" and xb.id = :brandAuxiliaryId");
				map.put("brandAuxiliaryId", auxiliaryId);
			}
		}
		if(isTehui != null && isTehui){
			sql.append(" and s.is_tehui is not null ");
		}
//		sql.append(" order by s.create_date desc ");
		sql.append(" order by s.is_top desc , s.modify_date desc ");
//		System.out.println(sql.toString());
		return nativeBaseDao.findPage(sql.toString(), map, pageable, Scheme.class);
	}
	
	@Override
	public int updateUpDown() {
		// TODO Auto-generated method stub
		String sql=" update 766_scheme xp set xp.is_marketable=(";
		sql+=" CASE WHEN xp.up_date is not null and xp.down_date is not null and DATE_FORMAT(NOW(),'%Y-%m-%d') >= DATE_FORMAT(xp.up_date,'%Y-%m-%d')  ";
		sql+=" and DATE_FORMAT(NOW(),'%Y-%m-%d')<=DATE_FORMAT(xp.down_date,'%Y-%m-%d') ";
		sql+=" THEN 1 ";
		sql+=" WHEN xp.up_date is not null and xp.down_date is null and DATE_FORMAT(NOW(),'%Y-%m-%d') >= DATE_FORMAT(xp.up_date,'%Y-%m-%d') ";
		sql+=" THEN 1 ";
		sql+=" WHEN xp.up_date is null and xp.down_date is not null and DATE_FORMAT(NOW(),'%Y-%m-%d')<=date_format(xp.down_date,'%Y-%m-%d') ";
		sql+=" THEN 1 ";
		sql+=" WHEN xp.up_date is null and xp.down_date is null ";				
		sql+=" THEN xp.is_marketable ";
		sql+=" ELSE 0 ";
		sql+=" END ";
		sql+=" ) ";
		Query query = entityManager.createNativeQuery(sql);
		int i=query.executeUpdate();
		System.out.println("update scheme marketable status !----!----!__!___!__!_!!__!_!_!_!_!_");
		return i;
	}
}
